package cn.java78.jdbc;

import cn.myschool.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
public class Seach1 {
    public static void main(String[] args) throws ParseException {
        //search("王","14","13",1,10);
        SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
       search("王","14","13",df.parse("2016-1-1"),df.parse("2020-1-1"),1,10);
        //ssearch("王","14","13",null,null,1,10);
       //srch(null,null,null,1,10);
    }

    /**
     *
     * @param sname
     * @param qq
     * @param telephone
     * @param start 入学时间起点
     * @param end   入学时间的终点
     * @param pageNo
     * @param pageSize
     */
    public static void search(String sname, String qq, String telephone, Date start,Date end, int pageNo, int pageSize){
        String sql="select * from s_student where  1=1";
        List<Object> params=new ArrayList<>();
        if(sname!=null&&!sname.equals("")){
            sql+=" and sname like \"%\"?\"%\" ";// sname like "%"?"%"
            //  like '%?%'
            //sql+=" and sname like concat('%',?,'%')";
            params.add(sname);
        }
       /* else{
            sql+=" and sname is null ";
        }*/
        if(qq!=null&&!qq.equals("")){
            sql+=" and qq like \"%\"?\"%\" ";
            params.add(qq);
        }
        if(telephone!=null&&!telephone.equals("")){
            sql+=" and telephone like \"%\"?\"%\" ";
            params.add(telephone);
        }
        if(start!=null){
            sql+=" and enterdate>=?";
            params.add(start);
        }
        if(end!=null){
            sql+=" and enterdate<=?";
            params.add(end);
        }
        sql+=" limit ?,?";
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);
        System.out.println(sql);
        System.out.println(params);
        Connection con=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            con= DBUtil.getConnection();
            ps=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            for(int i=0;i<params.size();i++){
                ps.setObject(i+1,params.get(i));
            }
            rs=ps.executeQuery();
            while (rs.next()){
                System.out.println(rs.getLong("stuno")+","+rs.getString("sname")+"."+rs.getDate("enterdate"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,con);
        }
    }
}
